package de.hmm.hwip.storage.service;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 *  Database Helper to generate DB if not exist and creates connection  
 * 
 * @author johannes mendel
 * @version 10.10.2012
 *
 */
public class HWipDatabaseHelper extends SQLiteOpenHelper {

	private final String TAG = "HWipDatabaseHelper";
	@SuppressWarnings("unused")
	private Context context;
	
	public HWipDatabaseHelper(Context context) {
		super(context, "hWip", null, 1);
		this.context = context; 
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(TAG, "create the Database");
		for(String csql : create)
			db.execSQL(csql);
		
		for(String isql : insert){
			Log.i(TAG, "isql: " + isql);
			db.execSQL(isql);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}
	
	/**SQL Commands to create Database. each String is another table. Foreign keys are enclosed */
	
	private final String cRadio = "CREATE TABLE radiotype (rt_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, rt_name TEXT);";
	 
	private final String cDevice = "CREATE TABLE device " + 
			"(device_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, " + 
			"rt_id_fk INT NOT NULL, " +
			"model_type TEXT NOT NULL, " +
			"manufacture TEXT, " +
			"d_specific TEXT, " +
			"response TEXT, " +
			"attr_1 TEXT, " +
			"attr_2 TEXT, " +
			"attr_3 TEXT, " +
			"attr_4 TEXT, "+
			"attr_5 TEXT, " +
			"attr_6 TEXT, " +
			"attr_7 TEXT, " +
			"attr_8 TEXT, " +
			"attr_9 TEXT, " +
			"attr_10 TEXT, " +
			"FOREIGN KEY (rt_id_fk) REFERENCES radiotype(rt_id) ON DELETE RESTRICT" +
		");";
	
	private final String cFloor = "CREATE TABLE floor (floor_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, f_num INT, f_description TEXT);";
	
	private final String cRoom = "CREATE TABLE room (room_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, floor_id_fk INT, r_description TEXT, " +
			"FOREIGN KEY (floor_id_fk) REFERENCES floor(floor_id) ON DELETE RESTRICT);";
	
	private final String cSpot = "CREATE TABLE spot (spot_id INT AUTO_INCREMENT PRIMARY KEY, room_id_fk INT, ip_address TEXT, " +
			"FOREIGN KEY (room_id_fk) REFERENCES room(room_id) ON DELETE RESTRICT);";
	
	private final String cSysDevice = "CREATE TABLE sys_device " + 
			"(sys_dev_id INT AUTO_INCREMENT PRIMARY KEY, " + 
			"spot_id_fk INT NOT NULL, " +
			"device_id_fk INT NOT NULL, " +
			"sn_addr TEXT, " +
			"sys_specific TEXT, " +
			"attr_value_1 TEXT, " +
			"attr_value_2 TEXT, " +
			"attr_value_3 TEXT, " +
			"attr_value_4 TEXT, " +
			"attr_value_5 TEXT, " +
			"attr_value_6 TEXT, " +
			"attr_value_7 TEXT, " +
			"attr_value_8 TEXT, " +
			"attr_value_9 TEXT, " +
			"attr_value_10 TEXT, " +
			"FOREIGN KEY (spot_id_fk) REFERENCES spot(spot_id) ON DELETE RESTRICT, " +
			"FOREIGN KEY (device_id_fk) REFERENCES device(device_id) ON DELETE RESTRICT);";
	
	private final String cCommand = "CREATE TABLE command " +
			"(command_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, " + 
			"command TEXT NOT NULL, " +
			"attr TEXT NOT NULL, " +
			"response TEXT);";
		
	private final String cLinkDevCom = "CREATE TABLE link_device_command " + 
			"(device_id_fk INT NOT NULL, " +
			"command_id_fk INT NOT NULL, " +
			"FOREIGN KEY (device_id_fk) REFERENCES device(device_id) ON DELETE RESTRICT, " +
			"FOREIGN KEY (command_id_fk) REFERENCES command(command_id) ON DELETE RESTRICT, " +
			"PRIMARY KEY (device_id_fk, command_id_fk));";
	
	private final String cControl =  "CREATE TABLE controlscript " + 
			"(cs_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,  " +
			"command_id_fk INT NOT NULL, " +
			"sys_dev_id_fk INT NOT NULL, " +
			"cs_time TEXT, " +
			"nextscript INT, " +
			"value TEXT, " +
			"FOREIGN KEY (command_id_fk) REFERENCES command(command_id) ON DELETE RESTRICT, " +
			"FOREIGN KEY (sys_dev_id_fk) REFERENCES sys_device(sys_dev_id) ON DELETE RESTRICT);";
	  
	private final String cConDev = "CREATE TABLE connected_devices " + 
			"(cs_id_fk INT NOT NULL, " +
			"sys_dev_id_fk INT NOT NULL, " +
			"FOREIGN KEY (cs_id_fk) REFERENCES controlscript(cs_id) ON DELETE RESTRICT, " +
			"FOREIGN KEY (sys_dev_id_fk) REFERENCES sys_device(sys_dev_id) ON DELETE RESTRICT, " +
			"PRIMARY KEY (cs_id_fk, sys_dev_id_fk));";
	
	private final String[] create = {cRadio,cDevice,cFloor, cRoom, cSpot, cSysDevice, cCommand, cLinkDevCom, cControl, cConDev};
	
	/**SQL Commands to insert base data. Each String is another table. */
	private final String iFloor = "INSERT INTO floor (floor_id, f_num, f_description) " +
			"VALUES (1, 0, 'Ground floor');";
	
	private final String iRoom = "INSERT INTO room (room_id, floor_id_fk, r_description) " +
			"VALUES (1, 1, 'Office');";
	
	private final String iRadio = "INSERT INTO radiotype (rt_id, rt_name) " +
			"VALUES (1, 'ANT');";
	
	private final String iDevice = "INSERT INTO device (device_id, rt_id_fk, model_type, manufacture, d_specific, response, attr_1, attr_2, attr_3, attr_4, attr_5 ) " + 
			"VALUES (1, 1, 119, 'smartLAB', 'Scale', 'no', 'weight', 'hydration', 'bodyfat', 'bonemass', 'musclemass');";
	private final String iDevice1 = "INSERT INTO device (device_id, rt_id_fk, model_type, manufacture, d_specific, response, attr_1 ) " + 
			"VALUES (2, 1, 22, 'smartLAB', 'Blood Glucosemeter', 'no', 'bloodGlucose');";
	private final String iDevice2 = "INSERT INTO device (device_id, rt_id_fk, model_type, manufacture, d_specific, response, attr_1, attr_2, attr_3, attr_4 ) " + 
			"VALUES (3, 1, 18, 'smartLAB', 'Blood Presuremeter', 'no', 'systolicPressure', 'diastolicPressure', 'pulseRate', 'dateTimestamp');";
	
	private final String iCommand = "INSERT INTO command (command_id, command, attr, response) VALUES (1, 'setValue', 'weight', 0);";
	
	private final String iControl = "INSERT INTO controlscript (cs_id, command_id_fk, sys_dev_id_fk) VALUES (1, 1, 1);";
	
	private final String iConDev = "INSERT INTO connected_devices(sys_dev_id_fk, cs_id_fk) VALUES (1,1);";
	
	private final String iLinkDevCom = "INSERT INTO link_device_command(device_id_fk, command_id_fk) VALUES (1,1);";
	
	private final String iSysDev = "INSERT INTO sys_device(sys_dev_id, spot_id_fk, device_id_fk) VALUES (1,1,1);";
	
	private final String iSpot = "INSERT INTO spot(spot_id, room_id_fk, ip_address) VALUES (1,1,'192.168.1.1');";
	
	private final String[] insert = {iFloor, iRoom, iRadio, iDevice, iDevice1, iDevice2, iCommand, iControl, iConDev, iLinkDevCom, iSpot, iSysDev};
	
}
